package com.bjb.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;

public class ExcelUtil {
	// 默认单元格内容为数字时格式
	private static DecimalFormat df = new DecimalFormat("0");
	// 默认单元格格式化日期字符串
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	// 格式化数字
	private static DecimalFormat nf = new DecimalFormat("0.00");

	/**
	 * 直接从输入流中读取文件内容
	 *
	 * @param inStream
	 * @return
	 */
	public static ArrayList<ArrayList<Object>> readExcelFromStream(InputStream inStream, String suffix) {
		if (inStream == null) {
			return null;
		}
		if (suffix.endsWith("xlsx")) {
			// 处理ecxel2007
			return readExcelFromStream2007(inStream);
		} else if (suffix.endsWith("xls")) {
			// 处理ecxel2003
			return readExcelFromStream2003(inStream);
		}
		return null;
	}

	public static ArrayList<ArrayList<Object>> readExcel(File file) {
		if (file == null) {
			return null;
		}
		if (file.getName().endsWith("xlsx")) {
			// 处理ecxel2007
			return readExcel2007(file);
		} else if (file.getName().endsWith("xls")) {
			// 处理ecxel2003
			return readExcel2003(file);
		}
		return null;
	}

	/*
	 * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似 lists.get(0).get(0)表示过去Excel中0行0列单元格
	 */
	public static ArrayList<ArrayList<Object>> readExcel2003(File file) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row;
			HSSFCell cell;
			Object value;
			int lastCellNum = 0;
			for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if (row == null) {
					// 当读取行为空时
					if (i != sheet.getPhysicalNumberOfRows()) {// 判断是否是最后一行
						rowList.add(colList);
					}
					continue;
				} else {
					rowCount++;
				}
				if (i == sheet.getFirstRowNum()) {
					lastCellNum = row.getLastCellNum();
				}
				if (row.getFirstCellNum() > 0) {
					for (int k = 0; k < row.getFirstCellNum(); k++) {
						colList.add("");
					}
				}
				for (int j = row.getFirstCellNum(); j <= lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
						// 当该单元格为空
						colList.add("");
						continue;
					}
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							value = (int) cell.getNumericCellValue();
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							if (Math.floor(cell.getNumericCellValue()) == Math.ceil(cell.getNumericCellValue())) {
								value = (int) cell.getNumericCellValue();
							} else {
								value = cell.getNumericCellValue();
							}
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = Boolean.valueOf(cell.getBooleanCellValue());
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}// end switch
					colList.add(value);
				} // end for j
				rowList.add(colList);
			} // end for i

			return rowList;
		} catch (Exception e) {
			return null;
		}
	}

	/*
	 * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似 lists.get(0).get(0)表示过去Excel中0行0列单元格
	 * 读取.xls结尾的文件 从流中读取内容
	 */
	public static ArrayList<ArrayList<Object>> readExcelFromStream2003(InputStream inStream) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			HSSFWorkbook wb = new HSSFWorkbook(inStream);
			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row;
			HSSFCell cell;
			Object value;
			int lastCellNum = 0;
			for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if (row == null) {
					// 当读取行为空时
					if (i != sheet.getPhysicalNumberOfRows()) {// 判断是否是最后一行
						rowList.add(colList);
					}
					continue;
				} else {
					rowCount++;
				}
				if (i == sheet.getFirstRowNum()) {
					lastCellNum = row.getLastCellNum();
				}
				if (row.getFirstCellNum() > 0) {
					for (int k = 0; k < row.getFirstCellNum(); k++) {
						colList.add("");
					}
				}
				for (int j = row.getFirstCellNum(); j <= lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
						// 当该单元格为空
						colList.add("");
						continue;
					}
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							cell.setCellType(XSSFCell.CELL_TYPE_STRING);
							value = cell.getStringCellValue();
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							if (Math.floor(cell.getNumericCellValue()) == Math.ceil(cell.getNumericCellValue())) {
								cell.setCellType(XSSFCell.CELL_TYPE_STRING);
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue();
							}
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = Boolean.valueOf(cell.getBooleanCellValue());
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}// end switch
					colList.add(value);
				} // end for j
				rowList.add(colList);
			} // end for i

			return rowList;
		} catch (Exception e) {
			return null;
		}
	}

	public static ArrayList<ArrayList<Object>> readExcel2007(File file) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			int lastCellNum = 0;
			for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if(IsRowEmpty(row)){
					rowCount ++;
					continue;
				} else {
					rowCount ++;
				}
				if (i == sheet.getFirstRowNum()) {
					lastCellNum = row.getLastCellNum();
				}
				if (row.getFirstCellNum() > 0) {
					for (int k = 0; k < row.getFirstCellNum(); k++) {
						colList.add("");
					}
				}
				for (int j = row.getFirstCellNum(); j <= lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
						// 当该单元格为空
						colList.add("");
						continue;
					}
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							value = (int) cell.getNumericCellValue();
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							if (Math.floor(cell.getNumericCellValue()) == Math.ceil(cell.getNumericCellValue())) {
								value = (int) cell.getNumericCellValue();
							} else {
								value = cell.getNumericCellValue();
							}
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = Boolean.valueOf(cell.getBooleanCellValue());
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}// end switch
					colList.add(value);
				} // end for j
				rowList.add(colList);
			} // end for i

			return rowList;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 读取.xlsx结尾的文件 从流中读取
	 *
	 * @param inStream
	 * @return
	 */
	public static ArrayList<ArrayList<Object>> readExcelFromStream2007(InputStream inStream) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			XSSFWorkbook wb = new XSSFWorkbook(inStream);
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			int lastCellNum = 0;
			for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if(IsRowEmpty(row)){
					rowCount ++;
					continue;
				} else {
					rowCount ++;
				}
				if (i == sheet.getFirstRowNum()) {
					lastCellNum = row.getLastCellNum();
				}
				if (row.getFirstCellNum() > 0) {
					for (int k = 0; k < row.getFirstCellNum(); k++) {
						colList.add("");
					}
				}
				for (int j = row.getFirstCellNum(); j <= lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
						// 当该单元格为空
						colList.add("");
						continue;
					}
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							cell.setCellType(XSSFCell.CELL_TYPE_STRING);
							value = cell.getStringCellValue();
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							if (Math.floor(cell.getNumericCellValue()) == Math.ceil(cell.getNumericCellValue())) {
								cell.setCellType(XSSFCell.CELL_TYPE_STRING);
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue();
							}
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = Boolean.valueOf(cell.getBooleanCellValue());
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}// end switch
					colList.add(value);
				} // end for j
				rowList.add(colList);
			} // end for i

			System.out.println("读取结束...........................");
			return rowList;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 将数据写到实际存在的文件中去
	 *
	 * @param result
	 * @param path
	 */
	public static void writeExcel(ArrayList<ArrayList<Object>> result, String path) {
		if (result == null) {
			return;
		}
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		HSSFCellStyle headStyle = createHeadStyle(wb);// 表头样式
		HSSFCellStyle normalStyle = createNormalStyle(wb);// 普通单元格样式
		for (int i = 0; i < result.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			if (result.get(i) != null) {
				for (int j = 0; j < result.get(i).size(); j++) {
					HSSFCell cell = row.createCell(j);
					if (result.get(i).get(j) instanceof Integer) {
						cell.setCellValue((Integer) (result.get(i).get(j)));
					} else if (result.get(i).get(j) instanceof Date) {
						String date = sdf.format((Date) (result.get(i).get(j)));
						cell.setCellValue(date);
					} else if (result.get(i).get(j) instanceof Boolean) {
						cell.setCellValue((Boolean) (result.get(i).get(j)));
					} else {
						if (result.get(i).get(j) != null) {
							cell.setCellValue(result.get(i).get(j).toString());
						} else {
							cell.setCellValue("");
						}
					}
					if (i == 0) {
						cell.setCellStyle(headStyle);
					} else {
						cell.setCellStyle(normalStyle);
					}
				}
			}
		}
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		byte[] content = os.toByteArray();
		File file = new File(path);// Excel文件生成后存储的位置。
		if (!file.getParentFile().exists()) {
			file.getParentFile().mkdirs();
		}
		if (file.exists()) {
			file.delete();
		}
		OutputStream fos = null;
		try {
			fos = new FileOutputStream(file);
			fos.write(content);
			os.close();
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 将数据写到流中 ，并返回输入流
	 *
	 * @param result
	 */
	public static InputStream writeExcelToStream(ArrayList<ArrayList<Object>> result) {
		if (result == null) {
			return null;
		}
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		HSSFCellStyle headStyle = createHeadStyle(wb);// 表头样式
		HSSFCellStyle normalStyle = createNormalStyle(wb);// 普通单元格样式
		for (int i = 0; i < result.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			if (result.get(i) != null) {
				for (int j = 0; j < result.get(i).size(); j++) {
					HSSFCell cell = row.createCell(j);
					if (result.get(i).get(j) instanceof Integer) {
						cell.setCellValue((Integer) (result.get(i).get(j)));
					} else if (result.get(i).get(j) instanceof Date) {
						String date = sdf.format((Date) (result.get(i).get(j)));
						cell.setCellValue(date);
					} else if (result.get(i).get(j) instanceof Boolean) {
						cell.setCellValue((Boolean) (result.get(i).get(j)));
					} else {
						if (result.get(i).get(j) != null) {
							cell.setCellValue(result.get(i).get(j).toString());
						} else {
							cell.setCellValue("");
						}
					}
					if (i == 0) {
						cell.setCellStyle(headStyle);
					} else {
						cell.setCellStyle(normalStyle);
					}
				}
			}
		}
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		byte[] content = os.toByteArray();
		ByteArrayInputStream is = new ByteArrayInputStream(content);
		try {
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return is;
	}

	/**
	 * 将数据写到流中 ，并返回输入流 (可生成多张数据表)
	 *
	 * @param result
	 */
	public static InputStream writeExcelToStreamWithMultiForm(Map<String, ArrayList<ArrayList<Object>>> result) {
		if (result == null) {
			return null;
		}
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle headStyle = createHeadStyle(wb);// 表头样式
		HSSFCellStyle normalStyle = createNormalStyle(wb);// 普通单元格样式
		for (String key : result.keySet()) {
			HSSFSheet sheet = wb.createSheet(key);
			ArrayList<ArrayList<Object>> sheet_data = result.get(key);
			for (int i = 0; i < sheet_data.size(); i++) {
				HSSFRow row = sheet.createRow(i);
				if (sheet_data.get(i) != null) {
					for (int j = 0; j < sheet_data.get(i).size(); j++) {
						HSSFCell cell = row.createCell(j);
						if (sheet_data.get(i).get(j) instanceof Integer) {
							cell.setCellValue((Integer) (sheet_data.get(i).get(j)));
						} else if (sheet_data.get(i).get(j) instanceof Date) {
							String date = sdf.format((Date) (sheet_data.get(i).get(j)));
							cell.setCellValue(date);
						} else if (sheet_data.get(i).get(j) instanceof Boolean) {
							cell.setCellValue((Boolean) (sheet_data.get(i).get(j)));
						} else {
							if (sheet_data.get(i).get(j) != null) {
								cell.setCellValue(sheet_data.get(i).get(j).toString());
							} else {
								cell.setCellValue("");
							}
						}
						if (i == 0) {
							cell.setCellStyle(headStyle);
						} else {
							cell.setCellStyle(normalStyle);
						}
					}
				}
			}
		}
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		byte[] content = os.toByteArray();
		ByteArrayInputStream is = new ByteArrayInputStream(content);
		try {
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return is;
	}




	/**
	 * 生成表头样式
	 *
	 * @return
	 */
	private static HSSFCellStyle createHeadStyle(HSSFWorkbook wb) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置背景颜色
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		// 设置边框
		cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
		// 设置字体
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");// 字体
		font.setFontHeightInPoints((short) 10);// 设置字体大小
		font.setBold(true);// 加粗显示
		cellStyle.setFont(font);

		return cellStyle;
	}

	/**
	 * 生成普通单元格样式
	 *
	 * @return
	 */
	private static HSSFCellStyle createNormalStyle(HSSFWorkbook wb) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置边框
		cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
		// 设置字体
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");// 字体
		font.setFontHeightInPoints((short) 10);// 设置字体大小
		cellStyle.setFont(font);

		return cellStyle;
	}

	/**
	 * 字体一，微软雅黑 10号字体 用于表格主体内容
	 * @param wb
	 * @return HSSFCellStyle
	 */
	public static HSSFCellStyle getStyle(HSSFWorkbook wb) {
		HSSFCellStyle style = getCenterStyle(wb);

		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 10); // 字体大小
		font.setFontName("微软雅黑"); // 字体为微软雅黑

		style.setFont(font);

		return style;
	}

	/**
	 * 字体二，宋体 15号 加粗，用于表格第一行标题
	 * @param wb
	 * @return HSSFCellStyle
	 */
	public static HSSFCellStyle getStyle2(HSSFWorkbook wb) {
		HSSFCellStyle style = getCenterStyle(wb);
		/*
		 * 设置字体
		 */
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 15); // 字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 黑体加粗
		font.setFontName("宋体"); // 字体为宋体

		style.setFont(font);

		return style;
	}

	/**
	 *  字体三，微软雅黑 11 加粗，用于表格第二行标题
	 * @param wb
	 * @return HSSFCellStyle
	 */
	public static HSSFCellStyle getStyle3(HSSFWorkbook wb) {
		HSSFCellStyle style = getCenterStyle(wb);

		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 11); // 字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 黑体加粗
		font.setFontName("微软雅黑"); // 字体为微软雅黑

		style.setFont(font);

		return style;
	}

	/**
	 *  全边框样式局中样式
	 * @param wb
	 * @return HSSFCellStyle
	 */
	public static HSSFCellStyle getCenterStyle(HSSFWorkbook wb) {
		HSSFCellStyle style = wb.createCellStyle();

		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右局中样式
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下局中
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

		return style;
	}

	public static boolean IsRowEmpty(XSSFRow row) {
		if(row == null){
			return true;
		}
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			XSSFCell cell = row.getCell(i);
			if (cell != null && cell.getCellType() != cell.CELL_TYPE_BLANK) {
				return false;
			}
		}
		return true;
	}

	public static DecimalFormat getDf() {
		return df;
	}

	public static void setDf(DecimalFormat df) {
		ExcelUtil.df = df;
	}

	public static SimpleDateFormat getSdf() {
		return sdf;
	}

	public static void setSdf(SimpleDateFormat sdf) {
		ExcelUtil.sdf = sdf;
	}

	public static DecimalFormat getNf() {
		return nf;
	}

	public static void setNf(DecimalFormat nf) {
		ExcelUtil.nf = nf;
	}
}